<?php
/**************************************************************************
 *
 *   Copyright 2010 American Public Media Group
 *
 *   This file is part of AIR2.
 *
 *   AIR2 is free software: you can redistribute it and/or modify
 *   it under the terms of the GNU General Public License as published by
 *   the Free Software Foundation, either version 3 of the License, or
 *   (at your option) any later version.
 *
 *   AIR2 is distributed in the hope that it will be useful,
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *   GNU General Public License for more details.
 *
 *   You should have received a copy of the GNU General Public License
 *   along with AIR2.  If not, see <http://www.gnu.org/licenses/>.
 *
 *************************************************************************/

require_once 'AIR2_CRUD_Controller.php';

/**
 * Project Controller
 *
 * @author rcavis
 * @package default
 */
class Project_Controller extends AIR2_CRUD_Controller {
    /* AIR2_CRUD_Controller variables */
    public $base_model_name = 'Project';
    public $base_view_name  = 'project';
    public $exposed_relations = array(
        'organizations' => 'ProjectOrg', //uri_name => rel_name (usually model name)
        'inquiries' => 'ProjectInquiry',
        'annotations' => 'ProjectAnnotation',
        'activity' => 'ProjectActivity',
        'outcomes' => 'ProjectOutcome',
        'submissions' => 'SrcResponseSet', //NOTE: since SrcResponseSet isn't directly related
        //to Project, the base class just adds a "->from(SrcResponseSet a)"
        //and we must specify the "where" clause ourselves
        'detail' => null, //NOTE: a blank rel_name indicates an HTML-only uri with no associated data
    );
    public $flatten_mapping = array(
        'ProjectInquiry' => 'Inquiry',
        //'ProjectOrg'     => 'Organization',
    );
    public $autoload_relations = array();
    public $column_blacklist = array();
    public $column_whitelist = array();
    public $taggable = true; // exposes project/1234/tags.json
    public $unique_name_col = 'prj_name';


    /**
     * Add custom relations to queries to this controller
     *
     * @param AIR2_Query $q
     * @param boolean $deeply (optional)
     */
    protected function _add_query_load_related(&$q, $deeply=true) {
        if (!$this->base_uuid) {
            $q->leftJoin('a.ProjectOrg');
        }
        elseif (!$this->rel_uri) { // base Project level
            $q->leftJoin('a.ProjectOrg');
        }
        elseif ($this->rel_uri === 'organizations') {
            $q->leftJoin('a.Organization o');
            $q->leftJoin('a.ContactUser u');
            $q->leftJoin('u.UserEmailAddress e WITH e.uem_primary_flag = true');
            $q->leftJoin('u.UserPhoneNumber p WITH p.uph_primary_flag = true');

            // flatten some org fields
            $q->addSelect('o.org_display_name as org_display_name');
            $this->extra_field_defs []= array('name' => 'org_display_name');
            $q->addSelect('o.org_uuid as org_uuid');
            $this->extra_field_defs []= array('name' => 'org_uuid');
            $q->addSelect('u.user_username as user_username');
            $this->extra_field_defs []= array('name' => 'user_username');
            $q->addSelect('u.user_uuid as user_uuid');
            $this->extra_field_defs []= array('name' => 'user_uuid');
        }
        elseif ($this->rel_uri === 'inquiries') {
            // prevent record-wise limit (fixes limit subquery)
            $q->getRoot()->setAttribute(Doctrine_Core::ATTR_QUERY_LIMIT, Doctrine_Core::LIMIT_ROWS);
            $q->leftJoin('a.Inquiry i');
            $q->leftJoin('i.CreUser u');
            $q->leftJoin('u.UserOrg uo WITH uo.uo_home_flag = true');
            $q->leftJoin('uo.Organization o');

            $recv_count = 'select count(*) from src_response_set where srs_inq_id = i.inq_id';
            $sent_count = '0'; // TODO
            $q->addSelect("($recv_count) as resp_count");
            $q->addSelect("($sent_count) as sent_count");
            $this->extra_field_defs []= array('name' => 'resp_count', 'type' => 'int');
            $this->extra_field_defs []= array('name' => 'sent_count', 'type' => 'int');
        }
        elseif ($this->rel_uri === 'annotations') {
            $q->leftJoin('a.CreUser u');
            $q->leftJoin('u.UserOrg uo WITH uo.uo_home_flag = true');
            $q->leftJoin('uo.Organization o');
        }
        elseif ($this->rel_uri === 'activity') {
            $q->leftJoin('a.User');
            $q->leftJoin('a.ActivityMaster');
            ProjectActivity::joinRelated($q, 'a');
        }
        elseif ($this->rel_uri === 'outcomes') {
            $q->leftJoin('a.CreUser');
            $q->leftJoin('a.UpdUser');
        }
        elseif ($this->rel_uri === 'submissions') {
            $prj_id = $this->_find_base()->prj_id;
            $id_query = "select pinq_inq_id from project_inquiry where pinq_prj_id = $prj_id";
            $q->leftJoin('a.SrcResponse r');
            $q->leftJoin('a.Source s');
            $q->leftJoin('a.Inquiry i');
            $q->where("a.srs_inq_id in ($id_query)");
            $q->groupBy('r.sr_srs_id'); // just return 1st src_response
        }

        parent::_add_query_load_related($q, $deeply);
    }


    /**
     * Override to add custom update queries
     *
     * @param Doctrine_Record $record (reference) the record object to modify
     * @param associative-array $data   the radix data to update the record with
     */
    protected function _update_record(&$record, $data) {
        if (!$this->base_uuid) {
            // create new project
            if (!isset($data['org_uuid'])) {
                $this->crud_error('Project Organization uuid required!');
            }
            else {
                $org = Doctrine::getTable('Organization')->findOneBy('org_uuid', $data['org_uuid']);
                if (!$org) $this->crud_error('Invalid Organization specified!');

                $record->ProjectOrg[0]->porg_contact_user_id = AIR2_REMOTE_USER_ID;
                $record->ProjectOrg[0]->Organization = $org;
                unset($data['org_uuid']);
            }
        }
        elseif ($this->rel_uri === 'organizations') {
            $conn = AIR2_DBManager::get_connection();
            $org_uuid = isset($data['org_uuid']) ? $data['org_uuid'] : null;
            $user_uuid = isset($data['user_uuid']) ? $data['user_uuid'] : null;
            unset($data['org_uuid']);
            unset($data['user_uuid']);

            if ($org_uuid) {
                $org_id = $conn->fetchOne('select org_id from organization where org_uuid = ?', array($org_uuid));
                $record->porg_org_id = $org_id;
            }
            if ($user_uuid) {
                $user_id = $conn->fetchOne('select user_id from user where user_uuid = ?', array($user_uuid));
                $record->porg_contact_user_id = $user_id;
            }
        }
        parent::_update_record($record, $data);
    }


    /**
     * Custom "limit" clause for submissions query, because doctrine creates
     * a very inefficient query
     *
     * @param AIR2_Query $q           (reference) query to modify
     * @param unknown $add_meta    (optional)
     * @param unknown $return_list (optional)
     * @return unknown
     */
    public function fetch_data(&$q, $add_meta=true, $return_list=false) {
        if ($this->rel_uri === 'submissions') {
            $limit = $q->getDqlPart('limit');
            $limit = count($limit) > 0 ? $limit[0] : null;
            $offset = $q->getDqlPart('offset');
            $offset = count($offset) > 0 ? $offset[0] : null;
            $order = $q->getDqlPart('orderby');
            $sort = count($order) > 0 ? $order[0] : null;
            $dir = $sort && count($order) > 1 ? $order[1] : null;

            if ($limit || $offset || $sort) {
                $q->removeDqlQueryPart('limit');
                $q->removeDqlQueryPart('offset');
                $q->removeDqlQueryPart('orderby');
                $this->_apply_submissions_limit($q, $limit, $offset, $sort, $dir);
            }
        }

        return parent::fetch_data($q, $add_meta, $return_list);
    }


    /**
     * Applies an optimized limit to a submissions query
     *
     * @param AIR2_Query $q     (reference) query to modify
     * @param int     $limit limit for the query
     * @param int     $start (optional) the results offset
     * @param string  $sort  (optional) sort field
     * @param string  $dir   (optional) ASC or DESC
     */
    private function _apply_submissions_limit(&$q, $limit, $start=null, $sort=null, $dir=null) {
        $prj_id = $this->_find_base()->prj_id;
        $id_query = AIR2_Query::create()
        ->select('a.srs_id')
        ->from('SrcResponseSet a')
        ->where('a.srs_inq_id in (select pinq_inq_id from project_inquiry where pinq_prj_id = ?)', array($prj_id))
        ->limit($limit);
        if ($start)
            $id_query->offset($start);
        if ($sort && $dir)
            $q->orderBy($sort.' '.$dir);

        $res = $id_query->fetchArray();
        $ids = array();
        foreach ($res as $record) {
            $ids []= $record['srs_id'];
        }

        $q->whereIn('srs_id', $ids);
    }


    /**
     * Custom data and views for html requests
     *
     * @param associative-array $data
     * @param string  $view_name
     * @return string html body
     */
    public function get_html_body($data, $view_name=null) {
        if ($this->base_uuid && !$this->rel_uri) { // base Project level
            $data = $this->_html_dashboard($data);
        }
        return parent::get_html_body($data, $view_name);
    }


    /**
     * Set the page title
     *
     * @param array   $head
     * @return array
     */
    public function get_html_head($head=array()) {
        if ($this->base_uuid) {
            $name = $this->_find_base()->prj_display_name;
            $head['title'] = AIR2_SYSTEM_DISP_NAME." - Project: $name";
        }
        return $head;
    }


    /**
     * Get data for HTML dashboard
     *
     * @param associative-array $data
     * @return associative-array
     */
    protected function _html_dashboard($data) {
        // organizations
        $q = $this->build_query($this->base_uuid, 'organizations');
        $q->orderBy('porg_cre_dtim DESC');
        $q->limit(4);
        $org_data = $this->fetch_data($q, true, true);

        // inquiries
        $q = $this->build_query($this->base_uuid, 'inquiries');
        $q->orderBy('resp_count DESC');
        $q->limit(5);
        $inq_data = $this->fetch_data($q, true, true);

        // annotations
        $q = $this->build_query($this->base_uuid, 'annotations');
        $q->orderBy('prjan_cre_dtim DESC');
        $q->limit(3);
        $annot_data = $this->fetch_data($q, true, true);

        // activity
        $q = $this->build_query($this->base_uuid, 'activity');
        $q->orderBy('pa_dtim DESC');
        $q->limit(5);
        $activity_data = $this->fetch_data($q, true, true);

        // tags
        $q = $this->airtag->create_tag_query($this->_find_base());
        $q->orderBy('added_dtim ASC');
        $tag_data = $this->airtag->exec_tag_query($q, true, true);

        // submissions
        $conn = AIR2_DBManager::get_connection();
        $id_query = "select pinq_inq_id from project_inquiry where pinq_prj_id = ?";
        $total = $conn->fetchOne("select count(*) from src_response_set where srs_inq_id in ($id_query)", array($this->_find_base()->prj_id), 0);
        $q = $this->build_query($this->base_uuid, 'submissions');
        $this->_apply_submissions_limit($q, 4, null, 'srs_cre_dtim', 'DESC');
        //$q->orderBy('srs_cre_dtim desc');
        //$q->limit(4);
        $subm_data = $this->fetch_data($q, true, true);
        $subm_data['total'] = $total;
        $subm_data['metaData']['limit'] = 4;

        // outcomes
        $q = $this->build_query($this->base_uuid, 'outcomes');
        $q->orderBy('prjo_dtim DESC');
        $q->limit(3);
        $out_data = $this->fetch_data($q, true, true);

        // stats
        $stats_data = $this->_stats_data($this->_find_base()->prj_id);

        return array(
            'base_url' => $this->uri_for('project').'/'.$this->base_uuid,
            'sub_srch' => $this->uri_for('search/responses',
                array('q' => 'prj_uuid='.$this->base_uuid)),
            'inq_srch' => $this->uri_for('search/inquiries',
                array('q' => 'prj_uuid='.$this->base_uuid)),
            'src_srch' => $this->uri_for('search/sources',
                array('q' => 'prj_uuid='.$this->base_uuid)),
            'proj'  => $data,
            'subm' => $subm_data,
            'inq' => $inq_data,
            'org' => $org_data,
            'out' => $out_data,
            'stats' => $stats_data,
            'annot' => $annot_data,
            'act' => $activity_data,
            'tag' => $tag_data,
        );
    }


    /**
     * Produce custom statistics for Project Dashboard
     *
     * @param int     $project_id
     * @return associative-array
     */
    private function _stats_data($project_id) {
        // Sources and Submissions (stats) panel data
        $conn = AIR2_DBManager::get_connection();
        $sent = 0; //$conn->fetchOne(''); TODO: tie to src_activity log
        $recv = $conn->fetchOne('select count(*) from src_response_set where srs_inq_id in (select pinq_inq_id from project_inquiry where pinq_prj_id = '.$project_id.')');
        $num_src = $conn->fetchOne('select count(distinct(srs_src_id)) from src_response_set where srs_inq_id in (select pinq_inq_id from project_inquiry where pinq_prj_id = '.$project_id.')');
        $pin_total = $conn->fetchOne('select count(*) from source where src_status = \'A\'');

        return array(
            'radix' => array(
                'prj_id' => $project_id,
                'SubmissionCount' => $recv,
                'SubmissionRate' => ($sent > 0) ? $recv/$sent : 'N/A',
                'SourceCount' => $num_src,
                'SourceRate' => ($pin_total > 0) ? $num_src/$pin_total : 'N/A',
            ),
            'metaData'=> $this->_create_metadata_basic(
                'prj_id',
                array( // column definitions
                    array('name'=>'prj_id'),
                    array('name'=>'SubmissionCount'),
                    array('name'=>'SubmissionRate'),
                    array('name'=>'SourceCount'),
                    array('name'=>'SourceRate'),
                )
            ),
        );
    }


}
